A schema provides a definition of a database table. The schema defines the structure and the type of contents that each data element within the structure can contain. For example, the schema for a structure that includes a table defines the size of a column in the table and the type of data in the column. The schema is generally stored in the database management system's (DBMS's) system database or catalog.
Modern database management systems, such as the DB2™ system developed by International Business Machines of Armonk, N.Y., support dynamic table schema charges by introducing self-describing table rows. In this system, each table is associated with a metadata table that contains information related to the table and each row's existing schema. The metadata is stored with the table, and therefore, each row is self-describing, i.e., reference to any source besides the table is not required. When the schema is altered, the latest, i.e., post alteration, table definition is recorded in the catalog. For any new row inserted into the table after the schema alteration, the corresponding metadata reflects the latest table definition. An existing row's metadata remains unchanged until a request to update that row is executed. At that time, the existing row's metadata is updated to reflect the latest table definition. Otherwise, the existing row's metadata reflects the pre-alteration table definition.
The above described dynamic schema alteration function performs well for its intended purpose and it is desirable to implement such functionality in a database system that includes tables that are not self-describing, i.e., table definitions are not stored with the table data. After the dynamic schema alteration function is invoked for the first time in such a database system, i.e., when the schema is altered for the first time, and when a first update to the table is performed, the DBMS appends both the old, i.e., original, and the new table definitions to the metadata table. Note that the table definitions are appended at this time for purposes of optimization. Those skilled in the art recognize that the table definitions can be appended at a different time, e.g., when the schema is first changed. Thus, after the first table update, each row is self-describing. As new rows are inserted into the table, they are stored based on the new table definition. As preexisting rows are updated, they are converted to the new table definition.
By storing table definitions, original and current, in the metadata and updating the metadata for a row “on-the-fly,” dynamic schema changes are performed without impairing database performance and/or availability. Each backup taken after the first update following the first schema change can be used as the source for data recovery or to clone another DBMS with the latest table definitions because the tables are self-describing. Problems arise, however, if the source for data recovery is a backup taken prior to the first update following the first schema change. In this backup, the tables are not self-describing, i.e., the original and current table definitions not appended to the metadata. Moreover, the original table definition is not stored in the catalog after the table definition has been altered. Accordingly, no record of the original table definition exists for this backup, and therefore, it cannot be used for data recovery.
Accordingly, a need exists for a system and method for preserving an original table schema in a database system that supports dynamic schema changes. The system and method should allow the database system to preserve a table definition before any alteration to the schema. The system and method should also allow any backup copy of the database, including one taken prior to a first update following a first schema change, to be used for data recovery. The present invention addresses such a need.